package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.DeliveryaddressDao;
import com.foreknow.elm.po.Deliveryaddress;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DeliveryaddressDaoImpl implements DeliveryaddressDao {
    private Connection con = null;
    //预处理
    private PreparedStatement pst=null;
    private ResultSet rs =null;
    /**
     * 根据用户编号查询所属送货地址
     * @param userId
     * @return
     */
    @Override
    public List<Deliveryaddress> listDeliveryAddressByUserId(String userId) throws Exception {
        String sql = "select * from deliveryAddress where userId=?";
        List<Deliveryaddress> list = new ArrayList<>();
        //创建数据库连接
        try {
            con =DBUtil.getConnection();
            pst =con.prepareStatement(sql);
            pst.setString(1,userId);
            rs =pst.executeQuery();
            while (rs.next()){
                Deliveryaddress deliveryaddress = new Deliveryaddress();
                deliveryaddress.setDaId(rs.getInt("daId"));
                deliveryaddress.setContactName(rs.getString("contactName"));
                deliveryaddress.setContactSex(rs.getInt("contactSex"));
                deliveryaddress.setContactTel(rs.getString("contactTel"));
                deliveryaddress.setAddress(rs.getString("address"));
                deliveryaddress.setUserId(rs.getString("userId"));
                list.add(deliveryaddress);
            }
        }finally {
            DBUtil.close(rs,pst);
        }
        return list;
    }

    /**
     * 根据送货地址编号查询送货地址
     * @param daId
     * @return
     */
    @Override
    public Deliveryaddress getDeliveryAddressById(Integer daId) throws Exception {
        String sql = "select * from deliveryAddress where daId=?";
        Deliveryaddress deliveryaddress =null;
        //创建数据库连接
        try {
            con =DBUtil.getConnection();
            pst =con.prepareStatement(sql);
            pst.setInt(1,daId);
            rs =pst.executeQuery();
            while (rs.next()){
                deliveryaddress = new Deliveryaddress();
                deliveryaddress.setDaId(rs.getInt("daId"));
                deliveryaddress.setContactName(rs.getString("contactName"));
                deliveryaddress.setContactSex(rs.getInt("contactSex"));
                deliveryaddress.setContactTel(rs.getString("contactTel"));
                deliveryaddress.setAddress(rs.getString("address"));
                deliveryaddress.setUserId(rs.getString("userId"));
            }
        }finally {
            DBUtil.close(rs,pst);
        }
        return deliveryaddress;
    }

    /**
     * 向送货地址表中添加一条记录
     * @param deliveryaddress
     * @return
     */
    @Override
    public int saveDeliveryAddress(Deliveryaddress deliveryaddress) throws Exception {
        int result =0;
        String sql = "insert into deliveryaddress (contactName,contactSex,contactTel,address,userId )values (?,?,?,?,?)";
        try {
            con =DBUtil.getConnection();
            pst =con.prepareStatement(sql);
            pst.setString(1,deliveryaddress.getContactName());
            pst.setInt(2,deliveryaddress.getContactSex());
            pst.setString(3,deliveryaddress.getContactTel());
            pst.setString(4,deliveryaddress.getAddress());
            pst.setString(5,deliveryaddress.getUserId());
            result =pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return result;
    }

    /**
     * 根据送货地址编号更新送货地址信息
     * @param deliveryaddress
     * @return
     */
    @Override
    public int updateDeliveryAddress(Deliveryaddress deliveryaddress) throws Exception {
        int result = 0;
        String sql = "update deliveryaddress set contactName=?,contactSex=?,contactTel=?,address=?,userId=? where daId=?";
        try {
            con =DBUtil.getConnection();
            pst =con.prepareStatement(sql);
            pst.setString(1,deliveryaddress.getContactName());
            pst.setInt(2,deliveryaddress.getContactSex());
            pst.setString(3,deliveryaddress.getContactTel());
            pst.setString(4,deliveryaddress.getAddress());
            pst.setString(5, deliveryaddress.getUserId());
            pst.setInt(6, deliveryaddress.getDaId());
            result =pst.executeUpdate();
        }finally {
            DBUtil.close(rs,pst);
        }
        return result;
    }

    @Override
    public int removeDeliveryAddress(Integer daId) throws Exception {
        int result =0;
        String sql = "delete from deliveryAddress where daId=? ";
        try {
            con =DBUtil.getConnection();
            pst =con.prepareStatement(sql);
            pst.setInt(1, daId);
            result =pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }

        return result;
    }
}
